Otázka č. 21 - Bloky, podmínky, cykly, kurzory a vyjímky

Bloky

• sekvence příkazů, která je uzavřena mezi klíčovými slovy BEGIN a END.
• Blok může obsahovat deklarace proměnných, výrazy, podmínky, cykly, a další bloky.
• Pomocí bloků lze vytvořit logické celky kódu, které mohou být samostatně spouštěny a znovu použity.

Části bloku

1. Deklarace proměnných - Definice proměnných, které budou v bloku použity.
2. Výkonná část - Obsahuje příkazy, které budou v bloku vykonány.
3. Vyjímková část - Slouží k zachycení a ošetření výjimek, které mohou nastat během vykonávání bloku.

BEGIN
    -- Deklarativní část
    DECLARE proměnná INT;

    -- Výkonná část
    proměnná := 10;
    SELECT * FROM tabulka WHERE sloupec = proměnná;

    -- Výjimková část
    EXCEPTION
        WHEN nějaká_výjimka THEN
            -- Ošetření výjimky
            ROLLBACK;
END;

Nepojmenované bloky

• základní bloky, které nemají vlastní název a jsou často používány jako součást jiných bloků nebo vnořené v jiných konstrukcích.

BEGIN
    DECLARE x INT DEFAULT 1;
    SET x = x + 1;
    SELECT x;
END;

Pojmenované bloky

• bloky, kterým je přiřazen název.
• V MariaDB jsou pojmenované bloky často procedury nebo funkce. Název bloku umožňuje jeho snadnější opakované použití a volání.

DELIMITER //
CREATE PROCEDURE moje_procedura()
BEGIN
    DECLARE x INT DEFAULT 1;
    SET x = x + 1;
    SELECT x;
END //
DELIMITER ;

Vnořené bloky

• bloky, které jsou definovány uvnitř jiných bloků. To umožňuje rozdělit komplexní logiku do menších, lépe spravovatelných částí.
• Vnořené bloky mohou být nepojmenované i pojmenované.

BEGIN
    DECLARE y INT DEFAULT 2;

    BEGIN
        DECLARE z INT;
        SET z = y * 2;
        SELECT z;
    END;

    SET y = y + 1;
    SELECT y;
END;

Podmínky

• umožňují řídit tok programu na základě specifických podmínek.
• V MariaDB se podmínky nejčastěji používají v rámci procedur a funkcí k provádění různých akcí podle hodnot proměnných nebo výsledků dotazů. - MariaDB podporuje několik typů podmínek, včetně IF a CASE.

IF podmínka

• používá k provedení bloku kódu, pokud je určitá podmínka splněna.
• Může mít také volitelnou větev ELSEIF a ELSE pro zpracování alternativních podmínek.

IF podmínka THEN
    -- Kód, který se vykoná, pokud je podmínka splněna
ELSEIF další_podmínka THEN
    -- Kód, který se vykoná, pokud je splněna další_podmínka
ELSE
    -- Kód, který se vykoná, pokud není splněna žádná z výše uvedených podmínek
END IF;

-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE check_value(IN num INT)
BEGIN
    IF num > 0 THEN
        SELECT 'Number is positive';
    ELSEIF num < 0 THEN
        SELECT 'Number is negative';
    ELSE
        SELECT 'Number is zero';
    END IF;
END //
DELIMITER ;

CASE podmínka

• možňuje zpracovat více podmínek a je podobná switch-case konstrukcím v jiných programovacích jazycích.
• CASE má dvě formy: jednoduchou a vyhledávací.

Jednoduchá forma

• porovnává jednu hodnotu s několika možnými hodnotami.

CASE výraz
    WHEN hodnota1 THEN
        -- Kód, který se vykoná, pokud výraz = hodnota1
    WHEN hodnota2 THEN
        -- Kód, který se vykoná, pokud výraz = hodnota2
    ELSE
        -- Kód, který se vykoná, pokud výraz neodpovídá žádné z výše uvedených hodnot
END CASE;

-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE check_grade(IN grade CHAR(1))
BEGIN
    CASE grade
        WHEN 'A' THEN
            SELECT 'Excellent';
        WHEN 'B' THEN
            SELECT 'Good';
        WHEN 'C' THEN
            SELECT 'Fair';
        WHEN 'D' THEN
            SELECT 'Poor';
        ELSE
            SELECT 'Fail';
    END CASE;
END //
DELIMITER ;

Vyhledávací forma

• umožňuje posuzovat složitější podmínky pro každou větev.

CASE
    WHEN podmínka1 THEN
        -- Kód, který se vykoná, pokud je podmínka1 splněna
    WHEN podmínka2 THEN
        -- Kód, který se vykoná, pokud je podmínka2 splněna
    ELSE
        -- Kód, který se vykoná, pokud není splněna žádná z výše uvedených podmínek
END CASE;

-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE check_number(IN num INT)
BEGIN
    CASE
        WHEN num > 0 THEN
            SELECT 'Number is positive';
        WHEN num < 0 THEN
            SELECT 'Number is negative';
        ELSE
            SELECT 'Number is zero';
    END CASE;
END //
DELIMITER ;

Cykly

• umožňují opakované provádění sady příkazů, dokud není splněna určitá podmínka.
• MariaDB podporuje několik typů cyklů: LOOP, WHILE, REPEAT a FOR.

LOOP cyklus

• provádí blok příkazů nekonečně, dokud není explicitně ukončen pomocí příkazu LEAVE.

LOOP
    -- Kód, který se opakuje
    IF podmínka THEN
        LEAVE;
    END IF;
END LOOP;

-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE loop_example()
BEGIN
    DECLARE x INT DEFAULT 0;

    loop_label: LOOP
        SET x = x + 1;
        IF x >= 10 THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END //
DELIMITER ;

• Výhoda
  • Flexibilní struktura, která může být ukončena kdykoli.
• Nevýhoda
  • Potenciál pro nekonečný cyklus, pokud není správně implementován podmíněný LEAVE.

WHILE cyklus

• provádí blok příkazů, dokud je podmínka pravdivá.

WHILE podmínka DO
    -- Příkazy
END WHILE;

-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE while_example()
BEGIN
    DECLARE x INT DEFAULT 0;

    WHILE x < 10 DO
        SET x = x + 1;
        SELECT x;
    END WHILE;
END //
DELIMITER ;

• Výhoda
  • Jasná a jednoduchá syntaxe pro opakování, dokud je podmínka pravdivá.
• Nevýhoda
  • Pokud podmínka nikdy nebude nepravdivá, může vést k nekonečnému cyklu.

REPEAT cyklus

• Provádí blok příkazů alespoň jednou a pak opakuje, dokud není podmínka splněna.

REPEAT
    -- Příkazy
UNTIL podmínka
END REPEAT;

-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE repeat_example()
BEGIN
    DECLARE x INT DEFAULT 0;

    REPEAT
        SET x = x + 1;
        SELECT x;
    UNTIL x >= 10
    END REPEAT;
END //
DELIMITER ;

• Výhody
  • Zaručuje, že se blok příkazů vykoná alespoň jednou.
• Nevýhody
  • Stejně jako u WHILE, může vést k nekonečnému cyklu, pokud podmínka nikdy nebude splněna.

FOR cyklus

• Používá se k opakování určitého bloku kódu pevně stanovený početkrát.

FOR x IN podmínka kolikrát má běžet DO
    -- Příkazy
END FOR;
-------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE for_example()
BEGIN
    DECLARE x INT;
    FOR x IN 1..10 DO
        SELECT x;
    END FOR;
END //
DELIMITER ;

• Výhody
  • Jednoduchá syntaxe pro opakování bloku kódu pevně stanovený početkrát.
• Nevýhody
  • Není vhodný pro situace, kdy není známý pevný počet opakování.

Kurzory

• používají k iteraci přes řádky výsledků dotazu a umožňují zpracovávat jednotlivé řádky jednu po druhé.

• To je užitečné, když potřebujete provádět složité operace nebo manipulace nad každým řádkem výsledku.

• Interní kurzory

  • Spravovány automaticky SQL serverem a uživatel k nim nemá přímý přístup. Používají se v běžných SQL dotazech.

• Externí kurzory

  • Definovány uživatelem a umožňují iteraci přes řádky výsledku dotazu explicitně, s přímou kontrolou nad kurzorem.

Základní příkazy

1.DECLARE CURSOR

• Definuje kurzor a přiřazuje mu výsledek dotazu.

DECLARE cursor_name CURSOR FOR SELECT sloupec1, sloupec2 FROM tabulka WHERE podmínka;

2. OPEN

• Otevření kurzoru a inicializace výsledkové sady.

OPEN cursor_name;

3. FETCH

• Načtení dalšího řádku z výsledkové sady kurzoru do proměnných.

FETCH cursor_name INTO proměnná1, proměnná2;

4. CLOSE

• Uzavření kurzoru a uvolnění zdrojů.

CLOSE cursor_name;

Příklad

DELIMITER //
CREATE PROCEDURE cursor_example()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_sloupec1 INT;
    DECLARE v_sloupec2 VARCHAR(100);

    DECLARE my_cursor CURSOR FOR SELECT sloupec1, sloupec2 FROM tabulka;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN my_cursor;

    my_loop: LOOP
        FETCH my_cursor INTO v_sloupec1, v_sloupec2;
        IF done THEN
            LEAVE my_loop;
        END IF;

        -- Zpracování řádku
        SELECT v_sloupec1, v_sloupec2;
    END LOOP;

    CLOSE my_cursor;
END //
DELIMITER ;

Kurzor s parametry

• mohou také používat parametry, což umožňuje větší flexibilitu při vytváření dynamických dotazů.

DELIMITER //
CREATE PROCEDURE cursor_with_param(IN input_param INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_sloupec1 INT;
    DECLARE v_sloupec2 VARCHAR(100);

    DECLARE my_cursor CURSOR FOR SELECT sloupec1, sloupec2 FROM tabulka WHERE sloupec1 = input_param;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN my_cursor;

    my_loop: LOOP
        FETCH my_cursor INTO v_sloupec1, v_sloupec2;
        IF done THEN
            LEAVE my_loop;
        END IF;

        -- Zpracování řádku
        SELECT v_sloupec1, v_sloupec2;
    END LOOP;

    CLOSE my_cursor;
END //
DELIMITER ;

Vyjímky

• slouží k zachycení a ošetření chyb nebo neočekávaných událostí, které nastanou během provádění SQL příkazů v uložených procedurách, funkcích nebo blocích kódu.

• Použití výjimek umožňuje lepší kontrolu nad chybami a umožňuje správně reagovat na různé situace, které mohou nastat během běhu programu.

• V MariaDB se ošetřování výjimek provádí pomocí HANDLERU.

• Zlepšení robustnosti a spolehlivosti aplikací.

• Poskytování uživatelsky přívětivějších chybových zpráv.

• Logování chyb a provádění nápravných opatření.

DECLARE handler_type HANDLER FOR condition_value_list statement;

• handler_type
  • Může být CONTINUE (pokračovat) nebo EXIT (ukončit).
• condition_value_list
  • Seznam chybových kódů nebo stavů, které mají být zachyceny.
• statement
  • Příkazy, které se mají vykonat, pokud je zachycena výjimka.

Příklad

DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Ošetření výjimky
        ROLLBACK;
        SELECT 'Chyba nastala a byla ošetřena.';
    END;

    -- Hlavní kód
    START TRANSACTION;
    -- Nějaké operace
    COMMIT;
END //
DELIMITER ;

Druhy výjimek

• SQLSTATE a místní chyby (local errors).

SQLSTATE

• kódy jsou standardizované pětimístné chybové kódy používané v SQL pro označení konkrétních typů chyb. Jsou univerzální napříč různými databázovými systémy.

• Příklady SQLSTATE kódů

  • 23000 - Porušení omezení integrity
  • 42000 - Chyba syntaxe
  • 01000 - Varování
  • 02000 - Žádné další řádky

DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
    -- Ošetření porušení omezení integrity
    ROLLBACK;
    SELECT 'Porušení omezení integrity.';
END;

Místní chyby

• jsou specifické pro daný DBMS a mohou být vyjádřeny číselnými kódy nebo specifickými výrazy.

• Příklady místních chyb v MariaDB

  • 1062 - Duplicitní klíč
  • 1146 - Neexistující tabulka

DECLARE EXIT HANDLER FOR 1062
BEGIN
    -- Ošetření duplicitního klíče
    ROLLBACK;
    SELECT 'Duplicitní klíč nalezen.';
END;